<?php
defined ( 'BASEPATH' ) or exit ( 'No direct script access allowed' );
class Order_model extends CI_Model {
	public function __construct() {
		parent::__construct ();
	}
	/*
	 * 根据订单状态和关键字返回订单数量
	 */
	public function count_orders($state,$del,$name){
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		if($state!=-2){
			$this->db->where('state', $state);
		}
		if($del!=-1){
			$this->db->where('del', $del);
		}
		if(!empty($name)){
			$this->db->group_start();
			$this->db->like('t_user.username', $name);
			$this->db->or_like('t_user.shopname', $name);
			$this->db->or_like('t_user.contact', $name);
			$this->db->group_end();
		}
		$query = $this->db->get('t_order');
		return $query->num_rows();
	}
	/*
	 * 根据订单状态、关键字和推广员ID返回订单数量
	 */
	public function count_orders_promoter($state,$del,$name,$promoterid){
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		if($state!=-2){
			$this->db->where('state', $state);
		}
		if($del!=-1){
			$this->db->where('del', $del);
		}
		$this->db->where('t_user.promoterid', $promoterid);
		if(!empty($name)){
			$this->db->group_start();
			$this->db->like('t_user.username', $name);
			$this->db->or_like('t_user.shopname', $name);
			$this->db->or_like('t_user.contact', $name);
			$this->db->group_end();
		}
		$query = $this->db->get('t_order');
		return $query->num_rows();
	}
	public function count_supplier_orders(){
		$query = $this->db->get('s_order');
		return $query->num_rows();
	}
	/*
	 * 根据订单状态、关键字和分页信息返回订单信息
	 */
	public function find_orders_by_state_del($state,$del,$name,$per_page,$offset){//
		$this->db->select('t_order.id,t_order.orderno,t_order.userid,t_user.shopname,t_user.contact,t_region.name as district,t_user.early_time,t_user.late_time,t_order.receive_time,t_order.note,t_order.order_time,t_order.payment_mode,t_order.payment_time,t_order.arrival_time,t_order.state,t_order.del,t_order.score,t_deliveryman.name as dname,t_order.merge,t_user.promoterid,t_promoter.name as promoter,t_order.second,t_user.lng,t_user.lat');
		$this->db->from('t_order');
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$this->db->join('t_region', 't_user.district = t_region.id','left');
		$this->db->join('t_deliveryman', 't_order.deliverymanid = t_deliveryman.id','left');
		$this->db->join('t_promoter', 't_promoter.id = t_user.promoterid','left');
		if($state!=-2){
			$this->db->where('state', $state);
		}
		if($del!=-1){
			$this->db->where('del', $del);
		}
		if(!empty($name)){
			if(is_numeric($name)){
				$this->db->where('t_user.id', $name);
			}else{
				$this->db->group_start();
				$this->db->like('t_user.username', $name);
				$this->db->or_like('t_user.shopname', $name);
				$this->db->or_like('t_user.contact', $name);
				$this->db->group_end();
			}
		}
		$this->db->limit($per_page, $offset);
		$this->db->order_by("order_time", "desc");
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据订单状态、关键字、推广员ID和分页信息返回订单信息
	 */
	public function find_orders_by_promoter($state,$del,$name,$promoterid,$per_page,$offset){//
		$this->db->select('t_order.id,t_order.orderno,t_order.userid,t_user.shopname,t_user.contact,t_region.name as district,t_user.early_time,t_user.late_time,t_order.receive_time,t_order.note,t_order.order_time,t_order.payment_mode,t_order.payment_time,t_order.arrival_time,t_order.state,t_order.del,t_deliveryman.name as dname,t_order.merge,t_order.second');
		$this->db->from('t_order');
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$this->db->join('t_region', 't_user.district = t_region.id','left');
		$this->db->join('t_deliveryman', 't_order.deliverymanid = t_deliveryman.id','left');
		$this->db->join('t_promoter', 't_promoter.id = t_user.promoterid','left');
		if($state!=-2){
			$this->db->where('state', $state);
		}
		if($del!=-1){
			$this->db->where('del', $del);
		}
		$this->db->where('t_user.promoterid', $promoterid);
		if(!empty($name)){
			if(is_numeric($name)){
				$this->db->where('t_order.id', $name);
			}else{
				$this->db->group_start();
				$this->db->like('t_user.username', $name);
				$this->db->or_like('t_user.shopname', $name);
				$this->db->or_like('t_user.contact', $name);
				$this->db->group_end();
			}
		}
		$this->db->limit($per_page, $offset);
		$this->db->order_by("order_time", "desc");
		$query = $this->db->get();
		return $query->result_array();
	}
	public function find_supplier_orders($per_page,$offset,$type,$order){//
		$this->db->select('s_order.id,s_order.orderno,s_supplier.name as sname,t_user.contact as uname,s_order.receive_time,s_order.note,s_order.order_time,s_order.payment_mode,s_order.payment_time,s_order.arrival_time,s_order.state,s_order.settle');
		$this->db->from('s_order');
		$this->db->join('s_supplier', 's_order.supplierid = s_supplier.id','left');
		$this->db->join('t_user', 's_order.userid = t_user.id','left');
		$this->db->limit($per_page, $offset);
		$this->db->order_by($type, $order);
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据订单ID返回订单信息
	 */
	public function load($id){//
		$this->db->select('t_order.id,t_order.orderno,t_order.userid,t_user.shopname,t_user.contact as name,t_user.late_time,t_user.cellphone,t_order.receive_time,t_order.note,t_order.order_time,t_order.payment_mode,t_order.payment_time,t_order.arrival_time,t_order.state,t_order.couponid,t_order.total,t_order.merge,t_order.del');
		$this->db->from('t_order');
		$this->db->where('t_order.id',$id);
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$query = $this->db->get();
		if ($row = $query->row_array()){
			return $row;
		}
		return array();
	}
	public function load_supplier_order($id){//
		$this->db->select('s_order.id,s_order.orderno,s_supplier.name as sname,t_user.contact as uname,s_order.receive_time,s_order.note,s_order.order_time,s_order.payment_mode,s_order.payment_time,s_order.arrival_time,s_order.state,s_order.settle');
		$this->db->from('s_order');
		$this->db->where('s_order.id',$id);
		$this->db->join('s_supplier', 's_order.supplierid = s_supplier.id','left');
		$this->db->join('t_user', 's_order.userid = t_user.id','left');
		$query = $this->db->get();
		if ($row = $query->row_array()){
			return $row;
		}
		return array();
	}
	/*
	 * 更新订单信息
	 */
	public function update($id,$order){
		$this->db->where('id', $id);
		$this->db->update('t_order', $order);
		return $this->db->affected_rows()>0;
	}
	/*
	 * 返回未确认的订单信息
	 */
	public function find_uncheck_orders(){
		$this->db->where('state=1 or (state=0 and payment_mode=3)');
		$query = $this->db->get("t_order");
		return $query->result_array();
	}
	/*
	 * 返回未完成的订单信息
	 */
	public function find_unfinish_orders(){
		$this->db->where('state=3 or state=5');
		$query = $this->db->get("t_order");
		return $query->result_array();
	}
	/*
	 * 一键更新订单信息
	 */
	public function update_orders($state){
		if($state==2){
			$sql="update t_order set state=2 where state=1 or (state=0 and payment_mode=3)";
		}else if($state==3){
			$sql="update t_order set state=3 where state=2";
		}else if($state==4){
			$sql="update t_order set state=4 where state=3 or state=5";
		}else{
			return array();
		}
	    $this->db->query($sql);
		return $this->db->affected_rows()>0;
	}
	public function update_supplier_order($id,$order){
		$this->db->where('id', $id);
		$this->db->update('s_order', $order);
		return $this->db->affected_rows()>0;
	}
	/*
	 * 根据日期得到要导出的订单
	 */
	public function find_export_orders($date){//
		$this->db->select('t_order.id,t_order.orderno,t_user.id as userid,t_user.shopname,t_user.contact as name,t_user.district,t_user.early_time ,t_user.late_time ,t_order.receive_time,t_order.payment_mode,t_order.note,t_order.total,t_order.couponid,t_user.address as address,t_user.cellphone as cellphone,t_user.service as service,t_deliveryman.name as dname,t_promoter.phone');
		$this->db->from('t_order');
		$this->db->where('t_order.state>=2');
		$this->db->where('t_order.del=0');
		$this->db->where("TO_DAYS(t_order.receive_time)-TO_DAYS('".$date."')=1");//可以用order_time
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$this->db->join('t_promoter', 't_user.promoterid = t_promoter.id','left');
		$this->db->join('t_deliveryman', 't_order.deliverymanid = t_deliveryman.id','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据日期和用户得到要导出的订单
	 */
	public function find_new_export_orders($date,$user){//
		$this->db->select('t_order.id,t_order.orderno,t_user.id as userid,t_user.shopname,t_user.contact as name,t_user.district,t_user.early_time ,t_user.late_time ,t_order.receive_time,t_order.payment_mode,t_order.note,t_order.total,t_order.couponid,t_user.address as address,t_user.cellphone as cellphone,t_user.service as service,t_deliveryman.name as dname,t_promoter.phone');
		$this->db->from('t_order');
		$this->db->where('t_order.state>=2');
		$this->db->where('t_order.del=0');
		$this->db->where('t_order.userid in ('.$user.')');
		$this->db->where("TO_DAYS(t_order.receive_time)-TO_DAYS('".$date."')=1");//可以用order_time
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$this->db->join('t_promoter', 't_user.promoterid = t_promoter.id','left');
		$this->db->join('t_deliveryman', 't_order.deliverymanid = t_deliveryman.id','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据日期得到下单的用户信息(用于导出分拣单)
	 */
	public function find_order_users_by_date($date){//
		$this->db->select('distinct(t_order.userid),t_user.shopname,t_region.name,t_user.district,t_user.early_time,t_user.late_time');
		$this->db->from('t_order');
		$this->db->where('t_order.state>=2');
		$this->db->where('t_order.del=0');//有效订单
		$this->db->where("TO_DAYS(t_order.receive_time)-TO_DAYS('".$date."')=1");
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$this->db->join('t_region', 't_user.district = t_region.id','left');
		$this->db->order_by("district");
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据日期得到下单的用户区域信息(用于导出分拣单)
	 */
	public function find_order_district_by_date($date){//
		$this->db->select('distinct(t_user.district),t_region.name');
		$this->db->from('t_order');
		$this->db->where('t_order.state>=2');
		$this->db->where('t_order.del=0');//有效订单
		$this->db->where("TO_DAYS(t_order.receive_time)-TO_DAYS('".$date."')=1");
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$this->db->join('t_region', 't_user.district = t_region.id','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据日期得到下单的用户信息(用于车辆调度)
	 */
	public function find_order_users($date){//
		$this->db->select('t_user.shopname,t_user.lng,t_user.lat,t_user.early_time,t_user.late_time');
		$this->db->from('t_order');
		$this->db->where('t_order.state>=2');
		$this->db->where('t_order.del=0');//有效订单
		$this->db->where('t_user.lng is not null');
		$this->db->where('t_user.lat is not null');
		$this->db->where('t_user.lng!=""');
		$this->db->where('t_user.lat!=""');
		$this->db->where("TO_DAYS(t_order.receive_time)-TO_DAYS('".$date."')=1");
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据起始日期得到下单的用户信息(用于查看客户位置)
	 */
	public function find_order_position_users_by_date($startdate,$enddate){//
		$this->db->select('distinct(t_order.userid) as id,t_user.shopname,t_user.lng,t_user.lat,t_user.early_time,t_user.late_time');
		$this->db->from('t_order');
		$this->db->where('t_order.state>=2');
		$this->db->where('t_order.del=0');//有效订单
		$this->db->where('t_user.lng is not null');
		$this->db->where('t_user.lat is not null');
		$this->db->where('t_user.lng!=""');
		$this->db->where('t_user.lat!=""');
		$this->db->where("DATE_FORMAT(t_order.order_time,'%Y-%m-%d')>=",$startdate);
		$this->db->where("DATE_FORMAT(t_order.order_time,'%Y-%m-%d')<=",$enddate);
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据起始日期得到要采购的订单
	 */
	public function find_export_order($startdate,$enddate){//
		$this->db->select('t_order.id,t_order.orderno,t_user.id as userid,t_user.shopname,t_user.contact as name,t_user.late_time ,t_order.receive_time,t_order.payment_mode,t_order.note,t_order.total,t_order.couponid,t_user.address as address,t_user.cellphone as cellphone,t_deliveryman.name as dname');
		$this->db->from('t_order');
		$this->db->where('t_order.state>=2');
		$this->db->where("t_order.receive_time>=",$startdate);
		$this->db->where("t_order.receive_time<=",$enddate);
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$this->db->join('t_deliveryman', 't_order.deliverymanid = t_deliveryman.id','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据日期和付款方式得到订单(该方法用于导出日报单)
	 */
	public function find_export_report($date,$payment){//
		if($payment==1){
			$this->db->where('payment_mode=2');//微信支付
		}
		if($payment==2){
			$this->db->where('(payment_mode=3 or payment_mode=4)');
		}
		$this->db->where('state>=2');
		$this->db->where("TO_DAYS(receive_time)-TO_DAYS('".$date."')=1");
		$query = $this->db->get("t_order");
		return $query->result_array();
	}
	/*
	 * 根据送货员id得到5星评价数量
	 */
	public function count_score($id){
		$this->db->where('deliverymanid',$id);
		$this->db->where('TO_DAYS(NOW())-TO_DAYS(t_order.receive_time)<=30');
		$this->db->where('score=5');
		$query = $this->db->get('t_order');
		return $query->num_rows();
	}
	/*
	 * 根据送货员id得到平均分
	 */
	public function get_avg_score($id){
		$this->db->select_avg('score');
		$this->db->where('deliverymanid',$id);
		$this->db->where('score!=0');
		$this->db->where('TO_DAYS(NOW())-TO_DAYS(t_order.receive_time)<=30');
		$query = $this->db->get('t_order');
		return $query->row_array();
	}
	/*
	 * 更新一个月内未评分的订单
	 */
	public function update_scores($order){
		$this->db->where('t_order.state=4');
		$this->db->where('t_order.score=0');
		$this->db->where('TO_DAYS(NOW())-TO_DAYS(t_order.receive_time)<=30');
		$this->db->update('t_order', $order);
		return $this->db->affected_rows()>0;
	}
	/*
	 * 根据日期和用户得到已完成订单的总金额(用于导出提成表和导出饭店统计)
	 */
	public function get_total_by_user_date($userid,$startdate,$enddate){
		$this->db->select_sum('total');
		$this->db->where('userid',$userid);
		$this->db->where('state=4');
		$this->db->where('del=0');//有效订单
		$this->db->where("DATE_FORMAT(order_time,'%Y-%m-%d')>=",$startdate);
		$this->db->where("DATE_FORMAT(order_time,'%Y-%m-%d')<=",$enddate);
		$query = $this->db->get('t_order');
		return $query->row_array();
	}
	/*
	 * 根据日期和用户得到已完成订单的数量(用于导出饭店统计)
	 */
	public function get_num_by_user_date($userid,$startdate,$enddate){
		$this->db->where('userid',$userid);
		$this->db->where('state=4');
		$this->db->where('del=0');//有效订单
		$this->db->where("DATE_FORMAT(order_time,'%Y-%m-%d')>=",$startdate);
		$this->db->where("DATE_FORMAT(order_time,'%Y-%m-%d')<=",$enddate);
		$query = $this->db->get('t_order');
		return $query->num_rows();
	}
	/*
	 * 返回总营业额
	 */
	public function get_totals(){
		$this->db->select_sum('total');
		$this->db->where('state=4');
		$this->db->where('del=0');
		$query = $this->db->get('t_order');
		return $query->row_array();
	}
	/*
	 * 根据下单日期返回该天的营业额
	 */
	public function get_total_by_date($date){
		$this->db->select_sum('total');
		$this->db->where('state>=2');
		$this->db->where('del=0');
		$this->db->where("DATE_FORMAT(order_time,'%Y-%m-%d')",$date);//改成这种
		$query = $this->db->get('t_order');
		return $query->row_array();
	}
	/*
	 * 返回下过订单的用户
	 */
	public function find_order_user(){
		$this->db->select('distinct(t_order.userid) as id,t_user.shopname,t_user.address,t_region.name,t_promoter.name as pname');
		$this->db->from('t_order');
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$this->db->join('t_region', 't_user.district = t_region.id','left');
		$this->db->join('t_promoter', 't_user.promoterid = t_promoter.id','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 *  返回下过订单且有坐标的用户(用于查看客户位置)
	 */
	public function find_order_position_users(){//
		$this->db->select('distinct(t_order.userid) as id,t_user.shopname,t_user.lng,t_user.lat,t_user.early_time,t_user.late_time');
		$this->db->from('t_order');
		$this->db->where('t_order.state>=2');
		$this->db->where('t_order.del=0');
		$this->db->where('t_user.lng is not null');
		$this->db->where('t_user.lat is not null');
		$this->db->where('t_user.lng!=""');
		$this->db->where('t_user.lat!=""');
		$this->db->join('t_user', 't_order.userid = t_user.id','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据用户和日期得到订单(用于导出饭店对账单)(有可能出现一天多个订单的情况)(可以参考下面的方法)
	 */
	public function get_orders_by_user_date($userid,$date){//
		$this->db->where('userid',$userid);
		$this->db->where("DATE_FORMAT(order_time,'%Y-%m-%d')",$date);
		$this->db->where('state!=-1');
		$query = $this->db->get('t_order');
		return $query->result_array();
	}
	/*
	 * 根据用户和起始日期得到最近的一个订单(用于导出饭店统计)
	 */
	public function get_order_by_user_date($userid,$startdate,$enddate){//
		$this->db->where('userid',$userid);
		$this->db->where('order_time>=',$startdate);
		$this->db->where('order_time<=',$enddate);
		$this->db->where('state=4');
		$this->db->where('del=0');//有效订单
		$this->db->order_by("order_time", "desc");
		$query = $this->db->get('t_order');
		return $query->row_array();
	}
	/*
	 * 根据用户ID得到最近用户的第一个订单(用于导出提成表)
	 */
	public function get_firstorder_by_user($userid){//
		$this->db->where('userid',$userid);
		$this->db->where('state=4');
		$this->db->order_by("order_time", "asc");
		$query = $this->db->get('t_order');
		return $query->row_array();
	}
	/*
	 * 根据用户ID和日期得到用户当天的第一个订单
	 */
	public function get_firstorder_by_user_date($userid,$date){//
		$this->db->where('userid',$userid);
		$this->db->where("TO_DAYS(order_time)-TO_DAYS('".$date."')=0");
		$this->db->where('state!=4');
		$this->db->where('state!=-1');
		$this->db->order_by("order_time", "asc");
		$query = $this->db->get('t_order');
		return $query->row_array();
	}
	/*
	 * 查询已支付的订单数
	 */
	public function find_orders_num($userid) {
		$this->db->where('userid',$userid);
		$this->db->where('state>=',2);
		$query = $this->db->get('t_order');
		return $query->num_rows();
	}
	/*
	 * 根据商品ID返回购买过该商品的用户
	 */
	public function find_order_user_by_product($productid){
		$query = $this->db->query("select * from(select t_order.userid,t_order.order_time,t_user.shopname,t_user.contact,t_user.address,t_user.cellphone from t_user,t_order
				    where  t_user.id=t_order.userid and
				    t_order.id in (select t_orderdetail.orderid from t_orderdetail where t_orderdetail.productid=".$productid.") order by t_order.order_time desc) temp group by userid order by order_time desc");
		return $query->result_array();
	}
	/*
	 * 根据商品ID返回购买过该商品的所有记录
	 */
	public function find_orderlist_by_product($productid){
		$query = $this->db->query("select t_order.id,t_order.userid,t_order.order_time,t_user.shopname,t_user.contact,t_user.address,t_user.cellphone from t_user,t_order
				    where  t_user.id=t_order.userid and
				    t_order.id in (select t_orderdetail.orderid from t_orderdetail where t_orderdetail.productid=".$productid.") order by t_order.order_time desc");
		return $query->result_array();
	}
	/*
	 * 检验订单编号是否存在
	 */
	public function check_orderno($orderno){
		//$this->db->select('id');
		$query = $this->db->get_where('t_order',array('orderno' => $orderno));
		if ($query->row_array()){
			return true;
		}
		return false;
	}
	/*
	 * 保存订单信息
	 */
	public function create($order) {
		$this->db->insert ( 't_order', $order );
		return $this->db->insert_id();//返回插入的ID
	}
}